package com.lin.missyou.repository;

import com.lin.missyou.model.Coupon;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface CouponRepository extends JpaRepository<Coupon, Long>, JpaSpecificationExecutor<Coupon> {

    @Query("select c from Coupon c \n" +
            "join c.categoryList ca\n" +
            "join Activity a on a.id = c.activityId\n" +
            "where ca.id = :cid\n" +
            "and a.startTime < :now\n" +
            "and a.endTime > :now\n")
    List<Coupon> findByCategory(@Param("cid") Long cid, @Param("now") Date now);
    /*
select * from coupon
                    join coupon_category on coupon.id = coupon_category.coupon_id
                    join category c on coupon_category.category_id = c.id
where c.id = 32
*/


    /*        @Query("select c from Activity a\n" +
                "join a.couponList c\n" +
                "where c.wholeStore = :isWhole\n" +
                "and c.startTime < :now\n" +
                "and c.endTime > :now")*/
    @Query("select c from Coupon c \n" +
            "join Activity a on a.id = c.activityId\n" +
            "where c.wholeStore = :isWhole\n" +
            "and a.startTime < :now\n" +
            "and a.endTime > :now")
    List<Coupon> getCouponByWholeStore(Boolean isWhole, Date now);


    // user,usercoupon,coupon
    @Query("select c from Coupon c\n" +
            "join UserCoupon uc on c.id = uc.couponId\n" +
            "join User  u on u.id = uc.userId\n" +
            "where u.id = :uid\n" +
            "and uc.status = 1 \n" +
            "and uc.orderId is null\n" +
            "and c.startTime <  :now\n" +
            "and c.endTime > :now ")
    List<Coupon> findMyAvailableCoupon(@Param("uid") Long uid, @Param("now") Date now);


    @Query("select c from Coupon c\n" +
            "join UserCoupon uc on c.id = uc.couponId\n" +
            "join User  u on u.id = uc.userId\n" +
            "where u.id = :uid\n" +
            "and uc.status = 2 \n" +
            "and uc.orderId is not null\n" +
            "and c.startTime <  :now\n" +
            "and c.endTime > :now ")
    List<Coupon> findMyUsedCoupon(@Param("uid") Long uid, @Param("now") Date now);

    // 过期的优惠卷 的status不一定准确
    @Query("select c from Coupon c\n" +
            "join UserCoupon uc on c.id = uc.couponId\n" +
            "join User  u on u.id = uc.userId\n" +
            "where u.id = :uid\n" +
            "and uc.status <> 2 \n" +
            "and uc.orderId is null\n" +
            "and c.endTime < :now ")
    List<Coupon> findMyExpiredCoupon(@Param("uid") Long uid, @Param("now") Date now);


}
